<?php
class IndexAction extends BaseAction
{


    /* 首页
     *
     */
    public function index()
    {
        // 数据库链接
        $db_conf_main = $this->get_db_config();

        // 检查安装
        $lock = getcwd() . '/install.lock';
        if( !file_exists($lock) )
        {
            U('Install/index', '', '', 1);
        }
        else
        {
            // 检查是否存在管理员账户
            $where_check_admin['power'] = array('eq', 1);
            $check_admin = M('users', '', $db_conf_main)->where($where_check_admin)->count();
            if($check_admin === FALSE)
            {
                $this->show_msg('error', '数据库错误：无法检查管理员账户', 0);
                die();
            }
            // 不存在则跳转至增加新用户
            if(!$check_admin)
            {
                $_SESSION['install'] = '1';
                U('Users/add_user', '', '', 1);
            }
            else
            {
                U('remote_management', '', '', 1);
            }
        }
     }


    /* 远程授权
	 *
	 */
	public function remote_management()
	{
        $this->head();
        // 检查登录
        $check_login = $this->check_login();
        if(!$check_login)
        {
            U('Users/login', '', '', 1);
        }
        // 获取远程IP（除127.0.0.1）
		$remote_IP = $_SERVER['HTTP_X_FORWARDED_FOR'] ?: $_SERVER['REMOTE_ADDR'];
		// 获取用户列表（Mysql用户，去重）
		C('DB_PREFIX', '');
        $db_conf_mysql = $this->get_db_config('mysql');
		$mysql_user_list = M('user', '', $db_conf_mysql)->distinct('User')->field('User')->order('User ASC')->select();
		$this->assign('mysql_user_list', $mysql_user_list);
		$this->assign('remote_IP', $remote_IP);

        $this->display();
	}


	/* 根据用户名查询当前IP的远程访问授权
	 *
	 */
	public function get_switch_status_by_user()
	{
		// 预定义返回数据
		$res['status'] = 0;
		$res['err_msg'] = '';
		$res['data'] = NULL;

		// 原表前缀
		$old_db_prefix = C('DB_PREFIX');
		// 新表前缀
		$new_db_prefix = '';
		// 数据库链接
		$db_conf = $this->get_db_config();
		C('DB_PREFIX', $new_db_prefix);
		$db_conf_mysql = $this->get_db_config('mysql');

		// 检测登录
        $check_login = $this->check_login();
		if(!$check_login)
		{
			$res['err_msg'] = '登录状态已失效，请重新登录';
			echo json_encode($res);
			die();
		}

		// 获取用户
		$user = trim( strval($_POST['user']) );
		if(!$user)
		{
			$res['err_msg'] = '用户名不能为空';
			echo json_encode($res);
			die();
		}
		// 若自动获取IP失败，则先获取手动设置的IP
		$remote_IP = $_SERVER['HTTP_X_FORWARDED_FOR'] ?: $_SERVER['REMOTE_ADDR'];
		if(!$remote_IP)
		{
			$remote_IP = strval( trim($_POST['REMOTE_ADDR']) );
		}
		// 若手动设置的IP也获取失败，则报错
		if(!$remote_IP)
		{
			$res['status'] = 2;
			$res['err_msg'] = '无法自动获取IP，请手动填写';
			echo json_encode($res);
			die();
		}
		// 查询授权状态
		$where_mysql_conf['Host'] = array('eq', $remote_IP);
		$where_mysql_conf['User'] = array('eq', $user);
		$mysql_conf = M('user', '', $db_conf_mysql)->where($where_mysql_conf)->find();
        if($mysql_conf)
		{
			$switch_status = 1;
		}
		else
		{
			$switch_status = 0;
		}

		$res = array(
			'status' => 1,
			'err_msg' => '',
			'data' => array(
				'user' => $user,
				'switch_status' => $switch_status
			)
		);
		echo json_encode($res);
	}


	/* 修改指定用户+IP的数据库远程访问权限
	 *
	 */
    public function remote_switch()
	{
		// 预定义返回数据
		$res['status'] = 0;
		$res['err_msg'] = '';
		$res['data'] = NULL;

		// 原表前缀
        $old_db_prefix = C('DB_PREFIX');
        // 新表前缀
        $new_db_prefix = '';
        // 数据库链接
        $db_conf = $this->get_db_config();
        C('DB_PREFIX', $new_db_prefix);
        $db_conf_mysql = $this->get_db_config('mysql');
		// 本地IP列表
		$local_IP_list = array(
			// 'localhost',
			// '::1',
			// '127.0.0.1'
		);

		// 检测事件调度器
		$check_event_scheduler = $this->check_event_scheduler();
		if(!$check_event_scheduler)
		{
			$res['err_msg'] = '事件调度器(Event Scheduler)未开启，请修改配置文件后重启MySQL，并重新执行操作';
			echo json_encode($res);
			die();
		}
        // 检测登录
        $check_login = $this->check_login();
        if(!$check_login)
		{
			$res['err_msg'] = '登录状态已失效，请重新登录';
			echo json_encode($res);
			die();
		}
		// 获取有效期
		$hour = intval($_POST['hour']);
		$hour = $hour<1 ? 1 : $hour;
		$hour = $hour>8 ? 8 : $hour;
		// 获取Mysql用户
		$mysql_user = strval( trim($_POST['user']) );
		if(!$mysql_user)
		{
			$res['err_msg'] = '参数丢失：用户名为空';
			echo json_encode($res);
			die();
		}
		// 获取开关状态
		$switch_status = intval($_POST['switch_status']);
		if( is_NULL($switch_status) )
		{
			$res['err_msg'] = '参数丢失：开关状态为空';
			echo json_encode($res);
			die();
		}
        // 若自动获取IP失败，则先获取手动设置的IP
		$remote_IP = $_SERVER['HTTP_X_FORWARDED_FOR'] ?: $_SERVER['REMOTE_ADDR'];
		if(!$remote_IP)
		{
			$remote_IP = strval( trim($_POST['REMOTE_ADDR']) );
		}
		// 若手动设置的IP也获取失败，则报错
		if(!$remote_IP)
		{
			$res['status'] = 2;
			$res['err_msg'] = '无法自动获取IP，请手动填写';
			echo json_encode($res);
			die();
		}
		// 计划任务：定时删除IP授权
		if( in_array($remote_IP, $local_IP_list) )
		{
			$res['err_msg'] = '本地IP无权操作';
			echo json_encode($res);
			die();
		}
		else
		{
			// 生成计划名
			$IP_replace = array(
				':' => '_',
				'.' => '_'
			);
			$plan_name = $_SESSION['rmt_user']['user_name'] . '_' . $mysql_user . '_' . strtr($remote_IP, $IP_replace);
			// 添加/删除任务
			switch($switch_status)
			{
				case 0:
					$query = "DROP EVENT IF EXISTS {$plan_name}";
					break;
				case 1:
					$date = time() + $hour * 3600;
					$date = date('Y-m-d H:i:s', $date);
					$query = "CREATE EVENT IF NOT EXISTS {$plan_name} ON SCHEDULE AT TIMESTAMP '{$date}' COMMENT '定时关闭远程授权' DO BEGIN\n\tDELETE FROM `user` WHERE `User`='{$mysql_user}' AND `Host`='{$remote_IP}';\n\tDELETE FROM `db` WHERE `User`='{$mysql_user}' AND `Host`='{$remote_IP}';\nEND";
					break;
			}
			M('user', '', $db_conf_mysql)->execute($query);
		}
		// 更新mysql.user表
        if($switch_status)
		{
			// 【打开远程登录】
            // 获取user表原始配置
            $where_user_conf_original['User'] = array('eq', $mysql_user);
			$where_user_conf_original['Host'] = array('eq', 'localhost');
			$user_conf_original = M('user', '', $db_conf_mysql)->where($where_user_conf_original)->find();
			if(!$user_conf_original)
            {
				$res['err_msg'] = '无法获取MySQL用户表原始配置';
				echo json_encode($res);
				die();
            }
            // 新建user表配置
            $data_user_open_remote = $user_conf_original;
            $data_user_open_remote['Host'] = $remote_IP;
            $user_open_remote = M('user', '', $db_conf_mysql)->add($data_user_open_remote);
            if(!$user_open_remote)
			{
				$res['err_msg'] = "授权失败：用户 {$mysql_user} 在IP {$remote_IP} 上的远程访问已开启";
			}




			// 获取db表原始配置
			$where_db_conf_original['User'] = array('eq', $mysql_user);
			$where_db_conf_original['Host'] = array('eq', 'localhost');
			$db_conf_original = M('db', '', $db_conf_mysql)->where($where_db_conf_original)->select();
			if($db_conf_original === false)
			{
				$res['err_msg'] = '无法获取MySQL数据库表原始配置';
				echo json_encode($res);
				die();
			}
			// 若用户授权了库，则新建db表配置
			if($db_conf_original)
			{
				$data_db_open_remote = $db_conf_original;
				foreach($data_db_open_remote as $key=>$val)
				{
					$data_db_open_remote[$key]['Host'] = $remote_IP;
				}
				$open_remote = M('db', '', $db_conf_mysql)->addAll($data_db_open_remote);
				if(!$db_open_remote)
				{
					$res['err_msg'] = "授权失败：用户 {$mysql_user} 在IP {$remote_IP} 上的远程访问已开启";
				}
			}

			$res['status'] = 1;
			$res['err_msg'] = "用户 {$mysql_user} 在IP {$remote_IP} 上的远程访问授权成功";
			$res['data']['switch_status'] = 1;
		}
		else
		{
			// 【关闭远程登录】
            $where_close_remote_user['User'] = array('eq', $mysql_user);
			$where_close_remote_user['Host'] = array('eq', $remote_IP);
			$close_remote_user = M('user', '', $db_conf_mysql)->where($where_close_remote_user)->delete();
			if(!$close_remote_user)
			{
				$res['switch_status'] = 1;
				$res['err_msg'] = "用户 {$mysql_user} 在IP {$remote_IP} 上的远程访问取消授权失败";
			}


			$where_close_remote_db['User'] = array('eq', $mysql_user);
			$where_close_remote_db['Host'] = array('eq', $remote_IP);
			$close_remote_db = M('db', '', $db_conf_mysql)->where($where_close_remote_db)->delete();
			if(!$close_remote_db)
			{
				$res['switch_status'] = 1;
				$res['err_msg'] = "用户 {$mysql_user} 在IP {$remote_IP} 上的远程访问取消授权失败";
			}

			$res['status'] = 1;
			$res['err_msg'] = "用户 {$mysql_user} 在IP {$remote_IP} 上的远程访问取消授权成功";
			$res['data']['switch_status'] = 0;
		}
        // 刷新表权限
		M('user', '', $db_conf_mysql)->query("flush privileges");
		M('db', '', $db_conf_mysql)->query("flush privileges");
		// 记录日志
		if($res['status'] == 1)
		{
			C('DB_PREFIX', $old_db_prefix);
            $operate_time = time();
			$data_add_log['user_id'] = $_SESSION['rmt_user']['id'];
			$data_add_log['add_time'] = $operate_time;
			$data_add_log['contents'] = date('Y-m-d H:i:s', $operate_time) . " ： {$res['err_msg']}。 管理员：{$_SESSION['rmt_user']['nickname']}";
			$add_log = M('operation_log', '', $db_conf)->add($data_add_log);
			if(!$add_log)
			{
				$res['err_msg'] .= ' - 日志写入失败';
			}
		}
		echo json_encode($res);
	}


	/* 已授权IP列表
	 *
	 */
	public function authed_ip_list()
	{
		// 原表前缀
        $old_db_prefix = C('DB_PREFIX');
        // 新表前缀
        $new_db_prefix = '';
		// 数据库链接
        $db_conf = $this->get_db_config();
		C('DB_PREFIX', $new_db_prefix);
        $db_conf_mysql = $this->get_db_config('mysql');
		// 本地IP列表
		$local_IP_list = array(
			'localhost',
			'::1',
			'127.0.0.1'
		);
		// 分页尺寸
		$page_size = 50;
		// 翻页按钮数量
		$button_amount = 5;

		$this->head();
        // 检查登录
        $check_login = $this->check_login();
        if(!$check_login)
        {
            U('Users/login', '', '', 1);
        }
		// 获取IP筛选
		$ip_filter = strval( trim($_GET['ip_filter']) );
		if( in_array($ip_filter, $local_IP_list) )
		{
			$this->show_msg('error', '本地IP无权操作', 0);
			die();
		}
		// 获取已授权的IP
		$where_authed_ip_list['Host'] = array('not in', $local_IP_list);
		if($ip_filter)
		{
			$where_authed_ip_list['Host'] = array('eq', $ip_filter);
		}
		$authed_ip_list = M('user', '', $db_conf_mysql)->where($where_authed_ip_list)->select();
		if($authed_ip_list === FALSE)
		{
			$this->show_msg('error', '数据库错误：无法获取已授权的IP', 0);
			die();
		}
		// 获取MySQL定时任务
		$query = 'SHOW EVENTS';
		$event_schedule_list = M('user', '', $db_conf_mysql)->query($query);
		if($event_schedule_list === FALSE)
		{
			$this->show_msg('error', '数据库错误：无法获取MySQL定时任务', 0);
			die();
		}
		// 将数组下标转为计划名
		if($event_schedule_list)
		{
			foreach($event_schedule_list as $m=>$n)
			{
				$index_name_arr = explode('_', $n['Name']);
				unset($index_name_arr[0]);
				$index_name = implode('_', $index_name_arr);
				$event_schedule_list_new[$index_name] = $n;
			}
			unset($event_schedule_list);
			$event_schedule_list = $event_schedule_list_new;
			unset($event_schedule_list_new);
		}
		// 循环处理数据
		foreach($authed_ip_list as $m=>$n)
		{
			// 生成计划名
			$IP_replace = array(
				':' => '_',
				'.' => '_'
			);
			$plan_name = strtr($n['Host'], $IP_replace);
			if(!$event_schedule_list[$plan_name])
			{
				unset($authed_ip_list[$m]);
				continue;
			}
			// 保留有用字段
			$authed_ip_list[$m] = array();
			$authed_ip_list[$m]['Host'] = $n['Host'];
			// 处理到期时间
			$authed_ip_list[$m]['deadline'] = $event_schedule_list[$plan_name]['Execute at'];
			// 处理授权人
			$author_arr = explode('_', $event_schedule_list[$plan_name]['Name']);
			$author = $author_arr[0];
			$authed_ip_list[$m]['author'] = $author;
		}
		// 授权人列表
		$author_list = array_column($authed_ip_list, 'author');
		$author_list = array_unique($author_list);
		$author_list = array_values($author_list);
		if($author_list)
		{
			// 获取授权人信息
			C('DB_PREFIX', $old_db_prefix);
			$field_author_info_list = array(
				'user_name',
				'nickname'
			);
			$where_author_info_list['user_name'] = array('in', $author_list);
			$author_info_list = M('users', '', $db_conf)->where($where_author_info_list)->field($field_author_info_list)->select();
			if($author_info_list === FALSE)
			{
				$this->show_msg('error', '数据库错误：无法获取授权人信息', 0);
				die();
			}
			// 将数组下标转换为授权人名
			if($author_info_list)
			{
				foreach($author_info_list as $m=>$n)
				{
					$author_info_list_new[($n['user_name'])] = $n;
				}
			}
			unset($author_info_list);
			$author_info_list = $author_info_list_new;
			unset($author_info_list_new);
			// 按授权人筛选
			$author_select = strval( trim($_GET['author']) );
			if(!$author_select)
			{
				$author_select = 'all';
			}
			// 处理授权人
			foreach($authed_ip_list as $m=>$n)
			{
				$author = $authed_ip_list[$m]['author'];
				$author = $author_info_list[$author]['nickname'];
				if(!$author || ($author_select != 'all' && ($author != $author_select)) )
				{
					unset($authed_ip_list[$m]);
					continue;
				}
				$authed_ip_list[$m]['author'] = $author;
			}
		}
		// 用户列表
		C('DB_PREFIX', $old_db_prefix);
		$where_user_list['useable'] = array('eq', 1);
		$user_list = M('users', '', $db_conf)->where($where_user_list)->select();
		if($user_list === FALSE)
		{
			$this->show_msg('error', '数据库错误：无法获取用户列表', 0);
			die();
		}
		$user_list = array_column($user_list, 'nickname');
		// 计算总条数
		$count = count($authed_ip_list);
		// 分页附加参数
		$page_button_parm = array('author' => $author);
		// 分页
		$PAGE = $this->page($count, $page_size, $button_amount, $page_button_parm);
		$cut = explode(',', $PAGE['limit']);
		$start = $cut[0];
		$length = $cut[1];
		$PAGE['data'] = array_slice($authed_ip_list, $start, $length);

		$this->assign('ip', $ip_filter);
		$this->assign('author', $author_select);
		$this->assign('author_list', $user_list);
		$this->assign('PAGE', $PAGE);
		$this->display();
	}


	/* 操作记录
	 *
	 */
	public function operation_log()
	{
		// 数据库链接
        $db_conf = $this->get_db_config();
		// 分页尺寸
		$page_size = 30;
		// 翻页按钮数量
		$button_amount = 5;

		$this->head();
        // 检查登录
        $check_login = $this->check_login();
        if(!$check_login)
        {
            U('Users/login', '', '', 1);
        }
		// 检查用户权限
		$user_power = $this->get_user_power();
		if($user_power != 1)
		{
			$this->show_msg('error', '该功能仅管理员可用', 0);
			die();
		}
		// 获取用户列表
		$field_user_list = array(
			'id',
			'nickname'
		);
		$where_user_list['useable'] = array('eq', 1);
		$user_list = M('users', '', $db_conf)->where($where_user_list)->field($field_user_list)->select();
		if($user_list === FALSE)
		{
			$this->show_msg('error', '数据库错误：无法获取用户列表', 0);
			die();
		}
		if(!$user_list)
		{
			$this->show_msg('error', '用户列表为空', 0);
			die();
		}
		// 循环处理数据
		foreach($user_list as $m=>$n)
		{
			// 将数组下标转为用户ID
			$user_list_new[($n['id'])] = $n;
			// 获取有效用户的ID列表
			$user_id_list[] = $n['id'];
		}
		unset($user_list);
		$user_list = $user_list_new;
		unset($user_list_new);
		// 获取用户ID
		$user_id = intval($_GET['user_id']);
		if($user_id)
		{
			if( in_array($user_id, $user_id_list) )
			{
				$user_id_list = array($user_id);
			}
		}
		else
		{
			$user_id = 'all';
		}
		// 计算总条数
		$where_count['user_id'] = array('in', $user_id_list);
		$count = M('operation_log', '', $db_conf)->where($where_count)->count();
		if($count === FALSE)
		{
			$this->show_msg('error', '数据库错误：无法获取操作记录', 0);
			die();
		}
		// 分页附加参数
		$page_button_parm = array('user_id' => $user_id);
		// 分页
		$PAGE = $this->page($count, $page_size, $button_amount, $page_button_parm);
		// 获取操作记录
		$limit = $PAGE['limit'];
		$where_operation_log['user_id'] = array('in', $user_id_list);
		$operation_log = M('operation_log', '', $db_conf)->where($where_operation_log)->order('id DESC')->limit($limit)->select();
		if($operation_log === FALSE)
		{
			$this->show_msg('error', '数据库错误：无法获取操作记录', 0);
			die();
		}
		// 循环处理数据
		foreach($operation_log as $m=>$n)
		{
			// 处理用户
			$operation_log[$m]['user'] = $user_list[($n['user_id'])]['nickname'];
			// 处理操作时间
			$operation_log[$m]['date'] = date('Y-m-d H:i:s', $n['add_time']);
		}
		$PAGE['data'] = $operation_log;

		$this->assign('user_id', $user_id);
		$this->assign('user_list', $user_list);
		$this->assign('PAGE', $PAGE);
		$this->display();
	}


}
